unit Class_Grade;

interface
uses Classes,SysUtils,ADODB,DataBaseConfig;
type
  Tgrade=class
   private
    FStud_id : Integer;
    Fcourse_id : Integer;
    Ftest_id : Integer;
    Fgrade : Integer;
   private
    FStud_code : string;
    FStud_name : string;
    Fcourse_code : string;
    Fcourse_name : string;
    Ftest_code : string;
    public
    procedure Insert(aADOConnetion:TADOConnection);
    procedure update(aADOConnetion:TADOConnection);
    procedure updateCN(aADOConnetion:TADOConnection);
    procedure delete(aADOConnetion:TADOConnection);
    function  GetStudID(aADOConnetion:TADOConnection):Integer;
    function  SelectStudIDByStudCode(aADOConnetion: TADOConnection): Integer;
    function  SelectCourseIDByCourseCode(aADOConnetion:TADOConnection):Integer;
    function  SelectTestIDByTestCode(aADOConnetion:TADOConnection):Integer;
    class procedure select(aADOConnetion:TADOConnection;var aList:TStringList;aSQL:string='');
    class procedure selectbystudcode(aADOConnetion:TADOConnection;var aList:TStringList;aSQL:string='');
    class procedure selectbycoursecode(aADOConnetion:TADOConnection;var aList:TStringList;aSQL:string='');
  published
    property Stud_id : Integer read FStud_id write FStud_id;
    property Stud_code : string read FStud_code write FStud_code;
    property Stud_name : string read FStud_name write FStud_name;
    property course_id : Integer read Fcourse_id write Fcourse_id;
    property test_id : Integer read Ftest_id write Ftest_id;
    property grade : Integer read Fgrade write Fgrade;
    property course_code : string read Fcourse_code write Fcourse_code;
    property course_name : string read Fcourse_name write Fcourse_name;
    property test_code : string read Ftest_code write Ftest_code; 
  public
    destructor Destroy; override;
    constructor Create;
  end;

implementation

{ Tgrade }

constructor Tgrade.Create;
begin

end;

procedure Tgrade.delete(aADOConnetion: TADOConnection);
var
  cSQL : string;
  cADOC:TADOCommand;
begin
   try
    cADOC:=TADOCommand.Create(nil);
    cADOC.Connection := aADOConnetion;
  with cADOC do
  begin
    cSQL := 'DELETE tbl_grade where stud_id=:stud_id and course_id =:course_id ';
    CommandText := cSQL;

    Parameters.ParamByName('stud_id').Value := self.stud_id;
    Parameters.ParamByName('course_id').Value := self.course_id;
  end;

  cADOC.Execute;
  finally
    FreeAndNil(cADOC);
end;
end;

destructor Tgrade.Destroy;
begin

  inherited;
end;

function Tgrade.GetStudID(aADOConnetion: TADOConnection): Integer;
var
  cSQL:string;
  cADOD:TADODataSet;//open
  //cADOC:TADOCommand;.execute;
begin
  try
    cADOD:=TADODataSet.Create(nil);
    cADOD.Connection := aADOConnetion;
    cADOD.CommandText := 'select Stud_id from tbl_Stud where stud_code=:stud_code';
    cADOD.Open;

    Result := cADOD.FieldByName('GetStud_id').AsInteger;

    if Result = 0 then Result := 1;
  finally
    FreeAndNil(cADOD);
  end;
end;

function TGrade.SelectStudIDByStudCode(aADOConnetion:TADOConnection):Integer;
var
  cSQL:string;
  cADOD:TADODataSet;//open
  //cADOC:TADOCommand;.execute;
begin
  Result := -1;
  try
    cADOD:=TADODataSet.Create(nil);
    cADOD.Connection := aADOConnetion;
    cADOD.CommandText := 'select Stud_id from tbl_Stud where Stud_Code=:Stud_Code';
    with cADOD.Parameters do
    begin
      ParamByName('Stud_Code').Value := self.Stud_Code;
    end;
    cADOD.Open;

    Result := cADOD.FieldByName('Stud_id').AsInteger;

    if Result = 0 then Result := -1;
  finally
    FreeAndNil(cADOD);
  end;
end;

procedure Tgrade.Insert(aADOConnetion: TADOConnection);
var
  cSQL : string;
  cADOC:TADOCommand;
begin
  try
    cADOC:=TADOCommand.Create(nil);
    cADOC.Connection := aADOConnetion;

    with cADOC do
    begin
      cSQL := 'insert into tbl_grade (course_id,stud_id,test_id,grade) values (:course_id,:Stud_id,:test_id,:grade)';
      CommandText := cSQL;
      Parameters.ParamByName('stud_id').Value := self.stud_id;
      Parameters.ParamByName('course_id').Value := self.course_id;
      Parameters.ParamByName('test_id').Value := self.test_id;
      Parameters.ParamByName('grade').Value := self.grade;
    end;

    cADOC.Execute;
  finally
    FreeAndNil(cADOC);
  end;
end;

class procedure Tgrade.select(aADOConnetion: TADOConnection;
  var aList: TStringList; aSQL: string);
var
  cgrade:Tgrade;
  ADOD:TADODataSet;
begin
  if aList = nil then Exit;

  try
    ADOD := TADODataSet.Create(nil);
    ADOD.Connection := aADOConnetion;
    if aSQL = '' then
    begin
      aSQL := 'select * from tbl_stud,tbl_course,tbl_grade,tbl_test where tbl_stud.stud_id = tbl_grade.stud_id  and tbl_grade.course_id = tbl_course.course_id and tbl_grade.test_id = tbl_test.test_id' ;
    end;

    ADOD.CommandText := aSQL;
    ADOD.Open;

    ADOD.First;
    while not ADOD.Eof do
    begin
      cgrade := TGrade.Create;
      aList.AddObject('',cgrade);
      cgrade.Stud_id := ADOD.FieldByName('stud_id').AsInteger;
      cgrade.Stud_code := ADOD.FieldByName('Stud_code').AsString;
      cgrade.Stud_name := ADOD.FieldByName('Stud_name').AsString;
      cGrade.course_id :=  ADOD.FieldByName('course_id').AsInteger;
      cgrade.course_code := ADOD.FieldByName('course_code').AsString;
      cgrade.course_name := ADOD.FieldByName('course_name').AsString;
      cgrade.test_id := ADOD.FieldByName('test_id').AsInteger;
      cgrade.test_code := ADOD.FieldByName('test_code').AsString;
      cgrade.grade := ADOD.FieldByName('grade').AsInteger;
      ADOD.Next;
    end;

  finally
    FreeAndNil(ADOD);
  end;

end;

procedure Tgrade.update(aADOConnetion: TADOConnection);
var
  cSQL : string;
  cADOC:TADOCommand;
begin
  try
    cADOC:=TADOCommand.Create(nil);
    cADOC.Connection := aADOConnetion;

  with cADOC do
  begin
    cSQL := 'update tbl_grade set grade=:grade where stud_id=:stud_id and course_id=:course_id';
    CommandText := cSQL;

    Parameters.ParamByName('stud_id').Value := self.Stud_ID;
    Parameters.ParamByName('grade').Value := self.grade;
    Parameters.ParamByName('course_id').Value := self.course_id;
  end;

    cADOC.Execute;
  finally
    FreeAndNil(cADOC);
  end;
end;

procedure Tgrade.updateCN(aADOConnetion: TADOConnection);
var
  cSQL : string;
  cADOC:TADOCommand;
begin
  try
    cADOC:=TADOCommand.Create(nil);
    cADOC.Connection := aADOConnetion;

  with cADOC do
  begin
    cSQL := 'update tbl_grade set grade=:grade where stud_id=:stud_id';
    CommandText := cSQL;

    Parameters.ParamByName('stud_id').Value := self.Stud_ID;
    Parameters.ParamByName('grade').Value := self.grade;

  end;

    cADOC.Execute;
  finally
    FreeAndNil(cADOC);
  end;

end;


function Tgrade.SelectCourseIDByCourseCode(
  aADOConnetion: TADOConnection): Integer;
var
  cSQL:string;
  cADOD:TADODataSet;//open
  //cADOC:TADOCommand;.execute;
begin
  Result := -1;
  try
    cADOD:=TADODataSet.Create(nil);
    cADOD.Connection := aADOConnetion;
    cADOD.CommandText := 'select course_id from tbl_course where course_code=:course_code';
    with cADOD.Parameters do
    begin
      ParamByName('course_code').Value := self.course_code;
    end;
    cADOD.Open;

    Result := cADOD.FieldByName('course_id').AsInteger;

    if Result = 0 then Result := -1;
  finally
    FreeAndNil(cADOD);
  end;
end;

function TGrade.SelectTestIDByTestCode(
  aADOConnetion: TADOConnection): Integer;
var
  cSQL:string;
  cADOD:TADODataSet;//open
  //cADOC:TADOCommand;.execute;
begin
  Result := -1;
  try
    cADOD:=TADODataSet.Create(nil);
    cADOD.Connection := aADOConnetion;
    cADOD.CommandText := 'select Test_id from tbl_Test where Test_code=:Test_code';
    with cADOD.Parameters do
    begin
      ParamByName('Test_code').Value := self.Test_code;
    end;
    cADOD.Open;

    Result := cADOD.FieldByName('Test_id').AsInteger;

    if Result = 0 then Result := -1;
  finally
    FreeAndNil(cADOD);
  end;
end;

class procedure Tgrade.selectbycoursecode(aADOConnetion: TADOConnection;
  var aList: TStringList; aSQL: string);
var
  cgrade:Tgrade;
  ADOD:TADODataSet;
begin
  if aList = nil then Exit;

  try
    ADOD := TADODataSet.Create(nil);
    ADOD.Connection := aADOConnetion;
    if aSQL = '' then
    begin
      aSQL := 'select * from tbl_stud,tbl_course,tbl_grade,tbl_test where tbl_stud.stud_id = tbl_grade.stud_id  and tbl_grade.course_id = tbl_course.course_id and tbl_grade.test_id = tbl_test.test_id order by course_code' ;
    end;

    ADOD.CommandText := aSQL;
    ADOD.Open;

    ADOD.First;
    while not ADOD.Eof do
    begin
      cgrade := TGrade.Create;
      aList.AddObject('',cgrade);

      cgrade.Stud_code := ADOD.FieldByName('Stud_code').AsString;
      cgrade.Stud_name := ADOD.FieldByName('Stud_name').AsString;
      cgrade.course_code := ADOD.FieldByName('course_code').AsString;
      cgrade.course_name := ADOD.FieldByName('course_name').AsString;
      cgrade.test_id := ADOD.FieldByName('test_id').AsInteger;
      cgrade.test_code := ADOD.FieldByName('test_code').AsString;
      cgrade.grade := ADOD.FieldByName('grade').AsInteger;
      ADOD.Next;
    end;

  finally
    FreeAndNil(ADOD);
  end;

end;

class procedure Tgrade.selectbystudcode(aADOConnetion: TADOConnection;
  var aList: TStringList; aSQL: string);
var
  cgrade:Tgrade;
  ADOD:TADODataSet;
begin
  if aList = nil then Exit;

  try
    ADOD := TADODataSet.Create(nil);
    ADOD.Connection := aADOConnetion;
    if aSQL = '' then
    begin
      aSQL := 'select * from tbl_stud,tbl_course,tbl_grade,tbl_test where tbl_stud.stud_id = tbl_grade.stud_id  and tbl_grade.course_id = tbl_course.course_id and tbl_grade.test_id = tbl_test.test_id order By stud_code' ;
    end;

    ADOD.CommandText := aSQL;
    ADOD.Open;

    ADOD.First;
    while not ADOD.Eof do
    begin
      cgrade := TGrade.Create;
      aList.AddObject('',cgrade);

      cgrade.Stud_code := ADOD.FieldByName('Stud_code').AsString;
      cgrade.Stud_name := ADOD.FieldByName('Stud_name').AsString;
      cgrade.course_code := ADOD.FieldByName('course_code').AsString;
      cgrade.course_name := ADOD.FieldByName('course_name').AsString;
      cgrade.test_id := ADOD.FieldByName('test_id').AsInteger;
      cgrade.test_code := ADOD.FieldByName('test_code').AsString;
      cgrade.grade := ADOD.FieldByName('grade').AsInteger;
      ADOD.Next;
    end;

  finally
    FreeAndNil(ADOD);
  end;

end;

end.

